import mysql.connector
import logging
import os
LOGPATH = "logs/"

class database:
    def __init__(self):
        self.initLogger()
        check_table_query = """
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_name = 'users' AND table_schema = 'aitiyu'
"""
        result = self.__exculate(check_table_query)
        table_exists = result[0][0] != 0
        # 打印结果
        if table_exists:
            logging.info("数据库准备完毕")
        else:
            logging.info("数据表不存在，准备初始化数据库")
            self.__createtable()
            logging.info("数据库初始化完毕")

    def initLogger(self):
        logformat='%(asctime)s %(levelname)s: %(message)s'
        os.makedirs(LOGPATH, exist_ok=True)
        logging.basicConfig(filename=LOGPATH+"/"+'app.log', level=logging.DEBUG,
                    format=logformat, encoding='utf-8')
        file_handler = logging.FileHandler(LOGPATH+"/"+'app.log', encoding='utf-8')  # 设置日志文件编码为UTF-8
        file_handler.setLevel(logging.DEBUG)  # 设置文件日志级别
        file_formatter = logging.Formatter(logformat)
        file_handler.setFormatter(file_formatter)
        console_handler = logging.StreamHandler()
        console_handler.setLevel(logging.INFO)  # 设置控制台日志级别
        console_formatter = logging.Formatter(logformat)
        console_handler.setFormatter(console_formatter)
        # 获取根日志记录器并添加控制台处理程序
        root_logger = logging.getLogger()
        root_logger.addHandler(console_handler)

    def ifExistUserInformation(self, xh):
        select_user_query = """
SELECT COUNT(*)
FROM users
WHERE xh = %s
"""
        result = self.__exculate(select_user_query, [xh,], method="get")
        return result[0][0] != 0

    def getUserInformation(self, xh):
        select_user_query = """
SELECT userid, token, device
FROM users
WHERE xh = %s
"""
        datas = (xh,)
        result = self.__exculate(select_user_query, datas=datas, method="get")
        logging.debug(result)
        logging.debug("获取数据成功")
        return result

    def __exculate(self, cmds, datas=[], method="get"):
        result = ""
        cnx = mysql.connector.connect(
            host='8.222.211.97',         # 数据库主机地址
            user='aitiyu',     # 数据库用户名
            password='sWp3btwEnnCDFMwr', # 数据库密码
            database='aitiyu'  # 数据库名称
        )
        cursor = cnx.cursor()
        if datas == []:
            cursor.execute(cmds)
        else:
            cursor.execute(cmds, datas)

        if method == "commit":
            cnx.commit()

        if method == "get":
            result = cursor.fetchall()

        # 关闭游标和连接
        cursor.close()
        cnx.close()

        return result

    def addUserInformation(self, user_data):
        # 定义插入用户记录的SQL语句
        insert_user_query = """
INSERT INTO users (xh, userid, token, device)
VALUES (%s, %s, %s, %s)
"""
        self.__exculate(insert_user_query, user_data, "commit")
        logging.debug(user_data)
        logging.debug("插入数据成功")

    def deleteUserInformation(self, user_data):
        # 定义插入用户记录的SQL语句
        delete_user_query = """
DELETE FROM users WHERE xh = %s;
"""
        self.__exculate(delete_user_query, user_data, "commit")
        logging.debug(user_data)
        logging.debug("删除数据成功")

    def __createtable(self):
        create_table_query = """
CREATE TABLE users (
    xh VARCHAR(50) PRIMARY KEY,
    userid VARCHAR(50),
    token VARCHAR(50),
    device VARCHAR(50)
)
"""
        self.__exculate(create_table_query, method="commit")

